package connectivity;

//import java.sql.Date;
import Model.FactuurMember;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.swing.JOptionPane;
import model.BarBon;
import model.BmiMeting;

import model.Branch;
//import main.BarApplication;
import model.Category;
import model.Course;
import model.GroupActivity;
//import model.Leden;

import model.Product;
import model.Member;
import model.Staff;
import model.Studio;
import model.mainCategory;
import model.Factuur;
//import net.proteanit.sql.DbUtils;

/**
 * The Query Manager is responsible for all the interaction with the database.
 * It uses the DataBaseManager
 *
 * @see(DbManager.class) to get to the database.
 */
public class QueryManager {

    private final DbManager dbmanager;

    public QueryManager(DbManager dbmanager) {
        this.dbmanager = dbmanager;
    }
//BEGIN ENOCH==================================================================

    public ResultSet lidCursusroosterList(int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT"
                    + " cursusrooster.idcursusrooster,"
                    + " product.naam,"
                    + " vestiging.vestigingnaam,"
                    + " studio.studionaam,"
                    + " cursusrooster.dag,"
                    + " cursusrooster.begingdatum,"
                    + " cursusrooster.einddatum,"
                    + " cursusrooster.van As tijd,"
                    + "'â‚¬ ' || product.prijs  prijs "
                    + " FROM"
                    + " public.product,"
                    + " public.cursusrooster,"
                    + " public.studio,"
                    + " public.vestiging,"
                    + " public.lidcursusrooster"
                    + " WHERE"
                    + " product.productnummer = cursusrooster.productnummer AND"
                    + " vestiging.idvestiging = cursusrooster.idvestiging AND"
                    + " studio.studionummer = cursusrooster.studionummer"
                    + " AND lidcursusrooster.idcursusrooster = cursusrooster.idcursusrooster"
                    + " AND lidcursusrooster.lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }

    public void setLidcursusrooster(int lid, int cursus) {

        String sql = "INSERT INTO lidcursusrooster(lidnummer, idcursusrooster) "
                + "VALUES (" + lid + ","
                + cursus + ")";
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
    }

    public ResultSet CususroosterList2(ResultSet result) {
        try {
            String sql = "SELECT"
                    + " cursusrooster.idcursusrooster,"
                    + " product.productnummer,"
                    + " product.naam,"
                    + " vestiging.idvestiging,"
                    + " vestiging.vestigingnaam,"
                    + " studio.studionaam,"
                    + " cursusrooster.dag,"
                    + " cursusrooster.begingdatum,"
                    + " cursusrooster.einddatum,"
                    + " cursusrooster.van As tijd,"
                    + "'€ ' || product.prijs  prijs "
                    + " FROM"
                    + " public.product,"
                    + " public.cursusrooster,"
                    + " public.studio,"
                    + " public.vestiging"
                    + " WHERE"
                    + " product.productnummer = cursusrooster.productnummer AND"
                    + " vestiging.idvestiging = cursusrooster.idvestiging AND"
                    + " studio.studionummer = cursusrooster.studionummer";

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }

    public void setOrder1(int lidnummer, int vestgingId, int productnummer) {
        String sql = "INSERT INTO barorder (opmerking, betaalmethode, ordernummer, lidnummer, datum, idvestiging)"
                + " VALUES ('',"
                + " '',"
                + " DEFAULT, "
                + lidnummer + ","
                + " CURRENT_DATE, "
                + vestgingId + ")"
                + " RETURNING ordernummer";
        int ordernummer = 0;
        try {
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                ordernummer = result.getInt(1);
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }

        String sql_orderregel = "INSERT INTO orderregel (productnummer, ordernummer, aantal, factuurnummer)"
                + " VALUES ("
                + productnummer
                + ","
                + ordernummer
                + ","
                + "1"
                + ","
                + "null"
                + ")";

        try {
            dbmanager.executeQuery(sql_orderregel);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);


        }

    }

    public GroupActivity getCoachActivityRooster(int activityid2) {
        GroupActivity groupactivity = null;

        try {
            String sql = "SELECT * FROM coachactiviteit WHERE idcoachactiviteit='" + activityid2 + "'";
            //String sql2 = "SELECT activiteitnaam FROM activiteit WHERE idactiviteit="+ activityid;

            ResultSet result = dbmanager.doQuery(sql);
            // ResultSet result2 =  dbmanager.doQuery(sql2);
            System.out.println(sql);
            if (result.next()) {
                groupactivity = new GroupActivity(
                        result.getInt("idcoachactiviteit"),
                        result.getInt("idactiviteitrooster"),
                        result.getInt("aantal"));

            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return groupactivity;
    }

    public void setCoachActivity(GroupActivity group) {

        String sql = "INSERT INTO coachactiviteit(idcoachactiviteit, idactiviteitrooster, datum, aantal) "
                + "VALUES (DEFAULT, "
                + group.getActivityrooster() + ","
                + "CURRENT_DATE,"
                + group.getAantal() + ")";
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
    }

    public ResultSet getCoachActivity(ResultSet result) {
        try {
            String sql = "SELECT idcoachactiviteit, ca.datum, vestigingnaam, studionaam, activiteitnaam, begintijd, eindtijd, aantal"
                    + " FROM coachactiviteit ca, activiteitrooster ar, vestiging v, studio s, activiteit a"
                    + " WHERE ca.idactiviteitrooster = ar.idactiviteitrooster"
                    + " AND ar.studionummer = s.studionummer"
                    + " AND ar.idvestiging = v.idvestiging"
                    + " AND ar.idactiviteit = a.idactiviteit"
                    + " ORDER BY ca.datum desc, begintijd";

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public void UpdateCoachActivity(GroupActivity activity) {

        String sql = "UPDATE coachactiviteit SET"
                + " aantal ='" + activity.getAantal()
                + "' WHERE idcoachactiviteit=" + activity.getCoachActiviteit();

        System.out.println(sql);

        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }

    }

    public Branch getVestigingStudio2(int studionummer) {
        Branch studio = new Branch();
        try {
            String sql = "SELECT studionummer, studionaam  "
                    + "FROM studio "
                    + "WHERE studionummer='" + studionummer + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                studio = new Branch(result.getInt("studionummer"),
                        result.getString("studionaam"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return studio;
    }

    public List<Branch> getVestigingList2() {
        List<Branch> vestiging = new ArrayList<Branch>();
        try {
            String sql = "SELECT * FROM vestiging ORDER BY vestigingnaam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Branch branch = new Branch(result.getInt("idvestiging"),
                        result.getString("vestigingnaam"),
                        result.getString("plaats"));

                vestiging.add(branch);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return vestiging;
    }

    public ResultSet OrderList2(int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT  bo.datum, bo.opmerking, p.naam,  o.aantal, p.prijs"
                    + " FROM barorder bo, orderregel o, product p"
                    + " WHERE o.ordernummer = bo.ordernummer"
                    + " AND o.productnummer = p.productnummer"
                    + " AND lidnummer=" + lid + ""
                    + " ORDER BY bo.datum desc";

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public ResultSet OrderListFilter2(String datum, int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT  bo.datum, bo.opmerking, p.naam, o.aantal, p.prijs"
                    + " FROM barorder bo, orderregel o, product p"
                    + " WHERE o.ordernummer = bo.ordernummer"
                    + " AND o.productnummer = p.productnummer"
                    + " AND datum='" + datum + "'"
                    + " AND lidnummer=" + lid;


            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public ResultSet VerkoopFilterProduct(int idvestiging, Date begindatum, Date einddatum) {
        ResultSet activitylog = null;
        try {
            String sql = "SELECT v.vestigingnaam, p.productnummer, p.naam, b.betaalmethode, o.aantal,"
                    + " sum(o.aantal * p.prijs) as totaalprijs"
                    + " FROM"
                    + " vestiging v"
                    + " INNER JOIN barorder b"
                    + " ON v.idvestiging = b.idvestiging"
                    + " INNER JOIN orderregel o"
                    + " ON b.ordernummer = o.ordernummer"
                    + " INNER JOIN product p"
                    + " ON o.productnummer = p.productnummer"
                    + " WHERE b.datum >= '" + begindatum + "'"
                    + " AND b.datum <= '" + einddatum + "'"
                    + " AND b.idvestiging =" + idvestiging + ""
                    + " AND b.betaalmethode = 'Op rekening'"
                    + " GROUP BY v.vestigingnaam, p.productnummer, p.naam,"
                    + " b.betaalmethode, o.aantal ORDER BY 2";
            activitylog = dbmanager.doQuery(sql);
            System.out.println(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return activitylog;
    }

    public ResultSet VerkoopFilterActivitiet(int idvestiging, Date begindatum, Date einddatum) {
        ResultSet activitylog = null;
        try {
            String sql = "";
            activitylog = dbmanager.doQuery(sql);
            System.out.println(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return activitylog;
    }

    public ResultSet VerkoopFilterCursus(int idvestiging, Date begindatum, Date einddatum) {
        ResultSet activitylog = null;
        try {
            String sql = "SELECT v.vestigingnaam, p.productnummer, p.naam, b.betaalmethode, o.aantal, b.datum"
                    + " FROM vestiging v INNER JOIN barorder b"
                    + " ON v.idvestiging = b.idvestiging"
                    + " INNER JOIN orderregel o"
                    + " ON b.ordernummer = o.ordernummer"
                    + " INNER JOIN product p"
                    + " ON o.productnummer = p.productnummer"
                    + " WHERE b.datum >= '" + begindatum + "'"
                    + "  AND b.datum <= '" + einddatum + "'"
                    + " AND b.idvestiging = " + idvestiging
                    + "  AND p.productnummer between 57 and 63"
                    + " GROUP BY v.vestigingnaam, p.productnummer, p.naam, b.betaalmethode, o.aantal, b.datum"
                    + " ORDER BY 2";
            activitylog = dbmanager.doQuery(sql);
            System.out.println(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return activitylog;
    }

    public void VerkoopCsvFiler(String jaar, String maand) {

        // String filename = "src/csv/file.csv";
        try {
            // FileWriter fw = new FileWriter(filename);
            String sql = "COPY(SELECT l.voornaam, l.achternaam, l.woonplaats, l.rekeningnummer, l.lidnummer, o.factuurnummer, sum(o.aantal * p.prijs)"
                    + " FROM lid l INNER JOIN barorder b"
                    + " ON b.lidnummer = l.lidnummer"
                    + " INNER JOIN orderregel o"
                    + " ON b.ordernummer = o.ordernummer"
                    + " INNER JOIN product p"
                    + " ON o.productnummer = p.productnummer"
                    + " WHERE to_char(b.datum, 'yyyy-mm-dd') LIKE '" + jaar + "-"+ maand +"-%' OR Exists (select * from abonnementstarteind)"
                    + " GROUP BY l.voornaam, l.achternaam, l.woonplaats, l.rekeningnummer, l.lidnummer, o.factuurnummer)"
                    + " TO '/Temp/Fitshape_Verkoop_" + jaar + "-" + maand + ".csv' WITH CSV HEADER";
            System.out.println(sql);
            dbmanager.executeQueryVoorBar(sql);

        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }


    }

    public ResultSet VerkoopFilterList(ResultSet result) {

        try {
            String sql = "SELECT v.vestigingnaam,"
                    //   + " p.productnummer,"
                    + " p.naam,"
                    + " b.betaalmethode,"
                    + " o.aantal,"
                    + " (p.prijs) As stukprijs,"
                    + " sum(o.aantal * p.prijs) as totaalprijs"
                    + " FROM"
                    + " vestiging v INNER JOIN barorder b"
                    + " ON v.idvestiging = b.idvestiging"
                    + " INNER JOIN orderregel o ON b.ordernummer = o.ordernummer"
                    + " INNER JOIN product p ON o.productnummer = p.productnummer"
                    + " WHERE b.datum = current_date"
                    + " GROUP BY v.vestigingnaam, p.productnummer, p.naam, b.betaalmethode, o.aantal"
                    + " ORDER BY 2 ";
            result = dbmanager.doQuery(sql);
            System.out.println(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return result;
    }

    public ResultSet GroupActivityFilter2(int idvestiging, String dag) {
        ResultSet activitylog = null;
        try {
            String sql = "SELECT ar.idactiviteitrooster, a.activiteitnaam, s.studionaam, ar.dag, ar.begintijd, eindtijd"
                    + " FROM studio s, activiteit a, activiteitrooster ar"
                    + " WHERE ar.idvestiging=" + idvestiging + ""
                    + " AND ar.dag='" + dag + "'"
                    + " AND s.studionummer= ar.studionummer"
                    + " AND a.idactiviteit= ar.idactiviteit"
                    + " ORDER BY ar.begintijd";
            activitylog = dbmanager.doQuery(sql);
            System.out.println(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return activitylog;
    }

    public ResultSet getCoachActivityFilter(String datum) {
        ResultSet result = null;
        try {
            String sql = "SELECT idcoachactiviteit, ca.datum, vestigingnaam, studionaam, activiteitnaam, begintijd, eindtijd, aantal"
                    + " FROM coachactiviteit ca, activiteitrooster ar, vestiging v, studio s, activiteit a"
                    + " WHERE ca.idactiviteitrooster = ar.idactiviteitrooster"
                    + " AND ar.studionummer = s.studionummer"
                    + " AND ar.idvestiging = v.idvestiging"
                    + " AND ar.idactiviteit = a.idactiviteit"
                    + " AND ca.datum='" + datum + "'"
                    + " ORDER BY begintijd";

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public void updateActivityRooster2(GroupActivity group) {

        String sql = "UPDATE activiteitrooster SET aantal=" + group.getAantal()
                + " WHERE idactiviteitrooster=" + group.getActivityID();
        System.out.println(sql);
        dbmanager.executeQuery(sql);
    }

    public ResultSet FactuurList2(int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT datum, factuurnummer"
                    + " FROM factuur"
                    + " WHERE lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public ResultSet FactuurListFilter2(String dat, int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT  datum, factuurnummer"
                    + " FROM factuur"
                    + " WHERE datum='" + dat + "'"
                    + " AND lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public List<Factuur> getFactuurNummer(String jaar, String maand) {

        List<Factuur> facturen = new ArrayList<Factuur>();

        try {

            String sql = "SELECT * FROM factuur WHERE datum LIKE'" + jaar + "-" + maand + "-%'";
            System.out.println(sql);
            ResultSet result = dbmanager.doQuery(sql);

            while (result.next()) {
                Factuur factuur = new Factuur(result.getInt("factuurnummer"),
                        result.getInt("lidnummer"),
                        result.getDate("datum"));
                facturen.add(factuur);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return facturen;
    }

    public List<BarBon> getBarBonFactuur(int Factuurnummer) {

        List<BarBon> bon = new ArrayList<BarBon>();

        try {
            String sql = "Select naam, aantal, prijs FROM orderregel"
                    + " JOIN barorder"
                    + " ON"
                    + " barorder.ordernummer = orderregel.ordernummer"
                    + " JOIN product"
                    + " ON"
                    + " orderregel.productnummer = product.productnummer"
                    + " where factuurnummer =" + Factuurnummer;

            ResultSet result = dbmanager.doQuery(sql);

            //Loopt door het resultaat en zet de resultaten een obect barbon welke weer toegevoegd wordt aan de arraylist bon
            while (result.next()) {
                BarBon barbon = new BarBon(result.getString("naam"), result.getInt("aantal"), result.getDouble("prijs"));
                bon.add(barbon);
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return bon;
    }

    public FactuurMember getFactuurLid(int Factuurnummer) {

        FactuurMember lidfactuur = null;

        try {
            String sql = " Select factuur.factuurnummer, factuur.datum,"
                    + " lid.lidnummer,lid.voornaam, lid.tussenvoegsel, lid.achternaam, lid.adres,"
                    + " lid.postcode, lid.woonplaats, product.naam, product.prijs As Pprijs, abonnement.*"
                    + " FROM"
                    + " orderregel"
                    + " JOIN"
                    + " barorder"
                    + " ON"
                    + " barorder.ordernummer = orderregel.ordernummer"
                    + " JOIN"
                    + " product"
                    + " ON"
                    + " orderregel.productnummer = product.productnummer"
                    + " JOIN factuur"
                    + " ON"
                    + " orderregel.factuurnummer = factuur.factuurnummer"
                    + " JOIN lid"
                    + " ON lid.lidnummer = factuur.lidnummer"
                    + " LEFT OUTER JOIN abonnement ON lid.abonnement = abonnement.abonnummer "
                    + " where"
                    + " factuur.factuurnummer =" + Factuurnummer;
            System.out.println(sql);
            ResultSet result = dbmanager.doQuery(sql);

            //Er kan er maar een gevonden worden omdat er gezocht wordt met een uniek medewerkernummer
            while (result.next()) {
                String tussenvoegsel;
                if (result.getString("tussenvoegsel") == null) {
                    tussenvoegsel = " ";
                } else {
                    tussenvoegsel = result.getString("tussenvoegsel").trim();
                }

                lidfactuur = new FactuurMember(result.getInt("factuurnummer"),
                        result.getDate("datum"),
                        result.getInt("lidnummer"),
                        result.getString("voornaam").trim(),
                        tussenvoegsel,
                        result.getString("achternaam").trim(),
                        result.getString("adres"),
                        result.getString("postcode"),
                        result.getString("woonplaats"),
                        result.getString("naam"),
                        result.getDouble("Pprijs"),
                        result.getString("abonnementSoortOmschrijving"),
                        result.getDouble("prijs"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return lidfactuur;
    }
    //-----------------------------------------------------------------------
    //
    //---------------------------------------------------------------------------

    public ResultSet getBMI2(int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT  datum, bmi"
                    + " FROM lidbmi"
                    + " WHERE lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public void setBMI2(BmiMeting bmi) {

        String sql = "INSERT INTO lidbmi(lidnummer, bmi, datum)"
                + "VALUES (" + bmi.getLidnummer() + ", " + bmi.getBmiMeting() + ", CURRENT_DATE)";
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
    }

    public ResultSet MBIFilter2(String dat, int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT  datum, bmi"
                    + " FROM lidbmi"
                    + " WHERE datum='" + dat + "'"
                    + " AND lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }

    // EINDE ENOCH=============================================================   
    public List<Branch> getVestigingList() {
        List<Branch> vestiging = new ArrayList<Branch>();
        try {
            String sql = "SELECT * FROM vestiging ORDER BY vestigingnaam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Branch branch = new Branch(result.getInt("idvestiging"),
                        result.getString("vestigingnaam"),
                        result.getString("plaats"));

                vestiging.add(branch);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return vestiging;
    }

//    public ResultSet GroupActivityFilter(int idvestiging, String datum) {
//        ResultSet activitylog = null;
//        try {
//            String sql = "SELECT ar.idactiviteitrooster, a.activiteitnaam, s.studionaam, ar.tijd, ar.aantal"
//                    + " FROM studio s, activiteit a, activiteitrooster ar"
//                    + " WHERE ar.idvestiging=" + idvestiging + ""
//                    + " AND ar.begindatum <='" + datum + "'"
//                    + " AND ar.einddatum >='" + datum + "'"
//                    + " AND s.studionummer= ar.studionummer"
//                    + " AND a.idactiviteit= ar.idactiviteit";
//            activitylog = dbmanager.doQuery(sql);
//            System.out.println(sql);
//        } catch (Exception e) {
//            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
//            e.printStackTrace(System.err);
//        }
//        return activitylog;
//    }
    public GroupActivity getGroupActivityRooster(int activityid) {
        GroupActivity groupactivity = null;

        try {
            String sql = "SELECT * FROM activiteitrooster WHERE idactiviteitrooster='" + activityid + "'";
            //String sql2 = "SELECT activiteitnaam FROM activiteit WHERE idactiviteit="+ activityid;

            ResultSet result = dbmanager.doQuery(sql);
            // ResultSet result2 =  dbmanager.doQuery(sql2);
            System.out.println(sql);
            if (result.next()) {
                groupactivity = new GroupActivity(
                        result.getInt("idactiviteitrooster"),
                        result.getInt("idactiviteit"),
                        result.getInt("idvestiging"),
                        result.getInt("studionummer"),
                        result.getString("dag"),
                        result.getTime("begintijd"),
                        result.getTime("eindtijd"),
                        result.getDate("begindatum"),
                        result.getDate("einddatum"));

            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return groupactivity;
    }

//    public GroupActivity getGroupActivity(int activityid) {
//        GroupActivity groupactivity = null;
//
//        try {
//
//            String sql = "SELECT * FROM activiteit WHERE idactiviteit='" + activityid + "'";
//
//            ResultSet result = dbmanager.doQuery(sql);
//
//            System.out.println(sql);
//            if (result.next()) {
//                groupactivity = new GroupActivity(result.getInt("idactiviteit"),
//                        result.getString("activiteitnaam"));
//            }
//        } catch (Exception e) {
//            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
//            e.printStackTrace(System.err);
//        }
//        return groupactivity;
//    }
    public Branch getVestigingStudio(int studionummer) {
        Branch studio = new Branch();
        try {
            String sql = "SELECT studionummer, studionaam  "
                    + "FROM studio "
                    + "WHERE studionummer='" + studionummer + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                studio = new Branch(result.getInt("studionummer"),
                        result.getString("studionaam"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return studio;
    }

    //Update table order inzage lid
    public ResultSet OrderList(int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT  datum, ordernummer, opmerking, betaalmethode"
                    + " FROM barorder"
                    + " WHERE lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    // In de OrderLijst zoeken op datum
    public ResultSet OrderListFilter(String datum, int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT  datum, ordernummer, opmerking, betaalmethode"
                    + " FROM barorder"
                    + " WHERE datum='" + datum + "'"
                    + " AND lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public ResultSet FactuurList(int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT datum, factuurnummer"
                    + " FROM factuur"
                    + " WHERE lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public ResultSet FactuurListFilter(String dat, int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT  datum, factuurnummer"
                    + " FROM factuur"
                    + " WHERE datum='" + dat + "'"
                    + " AND lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public ResultSet getBMI(int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT  datum, bmi"
                    + " FROM lidbmi"
                    + " WHERE lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;

    }

    public void setBMI(BmiMeting bmi) {

        String sql = "INSERT INTO lidbmi(lidnummer, bmi, datum)"
                + "VALUES (" + bmi.getLidnummer() + ", " + bmi.getBmiMeting() + ", CURRENT_DATE)";
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
    }

    public ResultSet MBIFilter(String dat, int lid) {
        ResultSet result = null;
        try {
            String sql = "SELECT  datum, bmi"
                    + " FROM lidbmi"
                    + " WHERE datum='" + dat + "'"
                    + " AND lidnummer=" + lid;

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }

    //-----------------------------------------ELMER----------------------------
    ///
    //-------------------------------------------------------------------------
    public ResultSet genereerFactuurLidAbonnement(Member lid) {
        // hier wordt gekeken in welke leeftijdcategorie het lid zit
        // Leeftijd 1 is junioren, 2 is volwassen, 3 is senior
        ResultSet rs = null;

        //check of het lid een abonnement heeft.
        if (lid.getAbonnement() == 1 || lid.getAbonnement() == 2) {





            try {

                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");


                Date d1 = format.parse(format.format(new Date()));
                Date d2 = format.parse(lid.getBirthday());
                long diff = d1.getTime() - d2.getTime();
                String sql;
                if ((diff / (24 * 60 * 60 * 1000)) < 6574) {
                    sql = "SELECT * FROM abonnement "
                            + "WHERE abonnementsoort = " + lid.getAbonnement()
                            + "AND categorie = 'Jeugd'";
                    rs = dbmanager.doQuery(sql);
                } else if ((diff / (24 * 60 * 60 * 1000)) > 6573 && diff / (24 * 60 * 60 * 1000) < 23741) {
                    sql = "SELECT * FROM abonnement "
                            + "WHERE abonnementsoort = " + lid.getAbonnement()
                            + "AND categorie = 'Volwassen'";
                    rs = dbmanager.doQuery(sql);
                } else if (diff / (24 * 60 * 60 * 1000) > 23740) {
                    sql = "SELECT * FROM abonnement "
                            + "WHERE abonnementsoort = " + lid.getAbonnement()
                            + "AND categorie = 'Senioren'";
                    rs = dbmanager.doQuery(sql);
                }



            } catch (Exception e) {
                JOptionPane.showMessageDialog(null, e);

            }



        }

        return rs;
    }

    public ResultSet genereerFactuurLid(Member lid, int jaar, int maand) {
        ResultSet result = null;
        try {
            String sql = "SELECT p.naam, o.aantal, p.prijs, o.aantal * p.prijs as totaal "
                    + "FROM product p "
                    + "INNER JOIN orderregel o "
                    + "ON p.productnummer = o.productnummer "
                    + "INNER JOIN barorder b "
                    + "ON o.ordernummer = b.ordernummer "
                    + "WHERE b.lidnummer = " + lid.getMemberId();



            result = dbmanager.doQuery(sql);


//        try {
//
//            String sql2 = "SELECT SUM(o.aantal * p.prijs) as prijs "
//                    + "FROM product p "
//                    + "INNER JOIN orderregel o "
//                    + "ON p.productnummer = o.productnummer "
//                    + "INNER JOIN barorder b "
//                    + "ON o.ordernummer = b.ordernummer "
//                    + "WHERE b.lidnummer = " + lid.getMemberId();
//
//            result = dbmanager.doQuery(sql2);
//            if(result.next()){
//            result.getInt(sql2);
//            
//            }
//
//
//        } catch (Exception e) {
//            JOptionPane.showMessageDialog(null, e);
//
//
//        }

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }


        return result;
    }

    public ResultSet genereerFactuurLidList(int lidnummer, String jaar, String maand) {
        ResultSet rs = null;
        try {
            String sql = "SELECT p.naam, o.aantal, p.prijs, o.aantal * p.prijs as totaal "
                    + "FROM product p "
                    + "INNER JOIN orderregel o "
                    + "ON p.productnummer = o.productnummer "
                    + "INNER JOIN barorder b "
                    + "ON o.ordernummer = b.ordernummer "
                    + "WHERE b.lidnummer =" + lidnummer + " AND "
                    + "to_char(b.datum, 'yyyy-mm-dd') LIKE'" + jaar + "-" + maand + "-%'";


            System.out.println(sql);
            rs = dbmanager.doQuery(sql);


        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }


        return rs;
    }

    public ResultSet genereerAbonementLidList(int lidnummer, String jaar, String maand) {
        ResultSet rs = null;
        try {
            String sql = "SELECT"
                    + " abonnement.categorie,"
                    + " abonnement.abonnementSoortOmschrijving,"
                    + " abonnement.prijs"
                    + " FROM"
                    + " abonnementstarteind, abonnement"
                    + " WHERE"
                    + " abonnement.abonnummer = abonnementstarteind.abonnement AND"
                    + " abonnementstarteind.datum = (SELECT MAX(datum) FROM abonnementstarteind WHERE lidnummer =" + lidnummer + " AND"
                    + " to_char(abonnementstarteind.datum, 'yyyy-mm-dd')<'" + jaar + "-" + maand + "-%') AND"
                    + " abonnementstarteind.lidnummer =" + lidnummer;

            System.out.println(sql);

            rs = dbmanager.doQuery(sql);


        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }


        return rs;
    }

    public ResultSet genereerFactuurTotaal(int lidnummer, int jaar, int maand) {
        ResultSet result = null;

        try {

            String sql = "SELECT SUM(o.aantal * p.prijs) as prijs "
                    + "FROM product p "
                    + "INNER JOIN orderregel o "
                    + "ON p.productnummer = o.productnummer "
                    + "INNER JOIN barorder b "
                    + "ON o.ordernummer = b.ordernummer "
                    + "WHERE b.lidnummer = " + lidnummer;

            result = dbmanager.doQuery(sql);



        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);


        }

        return result;
    }

    public ResultSet MemberList(ResultSet rs) {
        ResultSet result = null;
        try {
            String sql = "SELECT lidnummer, "
                    + "voornaam, tussenvoegsel, "
                    + "achternaam, geslacht, geboortedatum, status AS actief FROM lid "
                    + "ORDER BY lidnummer ASC";
            result = dbmanager.doQuery(sql);
            System.out.println(sql);
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }
//----------------------------------------- EINDE ALMER--------------------------------

    public ResultSet memberListSearchResult(int search) {
        ResultSet result = null;
        try {
            String sql = "SELECT lidnummer, "
                    + "voornaam, tussenvoegsel, "
                    + "achternaam, geslacht, geboortedatum, status AS actief FROM lid "
                    + "WHERE lidnummer = " + search;
            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }

    public Member getMember(int lidNummer) {

        Member lid = null;
        try {
            String sql = "SELECT * FROM lid WHERE lidnummer='"
                    + lidNummer + "'";

            ResultSet result = dbmanager.doQuery(sql);

            if (result.next()) {
                lid = new Member(result.getInt("lidnummer"),
                        result.getString("voornaam"),
                        result.getString("tussenvoegsel"),
                        result.getString("achternaam"),
                        result.getString("geslacht"),
                        result.getString("geboortedatum"),
                        result.getString("adres"),
                        result.getString("postcode"),
                        result.getString("woonplaats"),
                        result.getString("telefoonnummer"),
                        result.getString("mobieltelefoonnummer"),
                        result.getString("email"),
                        result.getString("rekeningnummer"),
                        result.getString("wachtwoordlid"),
                        result.getInt("idvestiging"),
                        result.getInt("abonnement"),
                        result.getBoolean("status"));
            } else {
                lid = new Member();
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return lid;
    }

    public void setMember(Member member) {

        String sql = "INSERT INTO lid(lidnummer, voornaam, tussenvoegsel, achternaam, geslacht, "
                + "geboortedatum, adres, postcode, woonplaats, telefoonnummer, mobieltelefoonnummer, email,"
                + " rekeningnummer, wachtwoordlid, status, idvestiging, abonnement) "
                + "VALUES ('"
                + member.getMemberId() + "','"
                + member.getFirstname() + "','"
                + member.getMiddlename() + "','"
                + member.getLastname() + "','"
                + member.getGender() + "','"
                + member.getBirthday() + "','"
                + member.getAddress() + "','"
                + member.getZip_code() + "','"
                + member.getResidence() + "','"
                + member.getTelephoneNumber() + "','"
                + member.getMobilePhone() + "','"
                + member.getEmail() + "','"
                + member.getBankAccount() + "','"
                + member.getPassword() + "',"
                + member.getStatus() + ","
                + member.getVestigingID() + ","
                + member.getAbonnement() + ")"
                + " RETURNING lidnummer";
        int lidnummer = 0;
        try {
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                lidnummer = result.getInt(1);
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }

        // Almer,, dubbel checken of uniek factuurnummer nodig is

        String sql_abonnement = "INSERT INTO abonnementstarteind(lidnummer, abonnement, datum)"
                + " VALUES ( " + lidnummer + ", " + member.getAbonnement() + ", CURRENT_TIMESTAMP )";

        try {
            dbmanager.executeQuery(sql_abonnement);

        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }



    }

    public void updateMember(Member member) {

        String sql = "UPDATE lid SET voornaam='" + member.getFirstname()
                + "', tussenvoegsel='" + member.getMiddlename()
                + "', achternaam='" + member.getLastname()
                + "', geboortedatum='" + member.getBirthday()
                + "', geslacht='" + member.getGender()
                + "', adres='" + member.getAddress()
                + "', postcode='" + member.getZip_code()
                + "', woonplaats='" + member.getResidence()
                + "', telefoonnummer='" + member.getTelephoneNumber()
                + "', mobieltelefoonnummer='" + member.getMobilePhone()
                + "', email='" + member.getEmail()
                + "', rekeningnummer='" + member.getBankAccount()
                + "', idvestiging=" + member.getVestigingID()
                + ", abonnement=" + member.getAbonnement()
                + ", status=" + member.getStatus()
                + " WHERE lidnummer=" + member.getMemberId();
        System.out.println(sql);
        dbmanager.executeQuery(sql);

        String sql_abonnement = "INSERT INTO abonnementstarteind(lidnummer, abonnement, datum)"
                + " VALUES (" + member.getMemberId() + ", " + member.getAbonnement() + ", CURRENT_TIMESTAMP )";

        try {
            dbmanager.executeQuery(sql_abonnement);

        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
    }

    //opzoeken van eerstvolgende vrije lidnummer
    public int getNewMemberID() {

        int memberID = -2;


        try {

            String sql = "SELECT MAX (lidnummer) AS Nummer FROM lid";
            ResultSet result = dbmanager.doQuery(sql);

            if (result.next()) {
                memberID = result.getInt("Nummer");
            }

        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }

        return memberID + 1;
    }

    public ResultSet getAbonnement(int abonnementNumber) {
        ResultSet result = null;
        try {
            String sql = "SELECT * FROM abonnement WHERE abonnummer = " + abonnementNumber;
            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }

    public Staff getMedewerker(int medewerkerNummer) {

        Staff medewerker = null;

        try {
            String sql = "SELECT * FROM medewerker  WHERE medewerkernummer='"
                    + medewerkerNummer + "'";

            ResultSet result = dbmanager.doQuery(sql);
            System.out.println(sql);
            //Er kan er maar een gevonden worden omdat er gezocht 
            //wordt met een uniek medewerkernummer
            if (result.next()) {
                medewerker = new Staff(result.getInt("medewerkernummer"),
                        result.getString("voornaam"),
                        result.getString("tussenvoegsel"),
                        result.getString("achternaam"),
                        result.getString("geslacht"),
                        result.getString("geboortedatum"),
                        result.getString("telefoonnummer"),
                        result.getString("mobieltelefoonnummer"),
                        result.getString("email"),
                        result.getString("wachtwoordmedewerker"),
                        result.getInt("idvestiging"),
                        result.getInt("idfunctie"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return medewerker;
    }

//------------------------------------------------------------------------------
//BEGIN CHRIST CODE/////////////////////////////////////////////////////////////
//------------------------------------------------------------------------------
    //------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------   
    public mainCategory getMainCategory(int mainCategoryId) {
        mainCategory mainCategory = null;
        try {
            String sql = "SELECT * FROM categorie WHERE maincategorie_id='" + mainCategoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                mainCategory = new mainCategory(result.getInt("maincategorie_id"),
                        result.getString("naam"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return mainCategory;
    }

//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------
    public Category getMainCategorys(int mainCategoryId) {
        Category category = null;
        try {
            String sql = "SELECT * FROM maincategorie WHERE maincategorie_id='" + mainCategoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                category = new Category(result.getInt("maincategorie_id"),
                        result.getString("naam"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return category;
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public List<Category> getCategories(int mainCategoryId) {
        List<Category> categories = new ArrayList<Category>();
        try {
            String sql = "SELECT * FROM categorie WHERE maincategorie_id ='" + mainCategoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Category category = new Category(result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getInt("maincategorie_id"));
                categories.add(category);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return categories;
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------
    public List<Product> getCurses() {
        List<Product> cursus = new ArrayList<Product>();
        try {
            String sql = "SELECT * FROM product WHERE categorie_id='" + 8 + "' ORDER BY naam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                cursus.add(new Product(result.getInt("productnummer"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getDouble("prijs")));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return cursus;
    }

    public Product getProduct(int productId) {
        Product product = new Product();
        try {
            String sql = "SELECT * FROM product WHERE productnummer='" + productId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                product = new Product(result.getInt("productnummer"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getDouble("prijs"),
                        result.getString("description"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return product;
    }

    public List<Product> getProducts(int categoryId) {
        List<Product> products = new ArrayList<Product>();
        try {
            String sql = "SELECT * FROM product WHERE categorie_id='" + categoryId + "' ORDER BY naam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                products.add(new Product(result.getInt("productnummer"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getDouble("prijs"),
                        result.getString("description")));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return products;
    }

    public void updateCursus(int productnummer) {
        try {
            String sql = "UPDATE cursusrooster"
                    + " SET huidigaantal=huidigaantal + 1"
                    + " WHERE productnummer=" + productnummer;

            dbmanager.executeQuery(sql);

        } catch (Exception e) {
        }

    }

    public List<mainCategory> getMainCategories() {
        List<mainCategory> mainCategories = new ArrayList<mainCategory>();
        try {
            String sql = "SELECT * FROM maincategorie";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                mainCategory mainCategory = new mainCategory(result.getInt("maincategorie_id"),
                        result.getString("naam"));
                mainCategories.add(mainCategory);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return mainCategories;
    }

    public Category getCategory(int mainCategoryId) {
        Category category = null;
        try {
            String sql = "SELECT * FROM categorie WHERE maincategorie_id='" + mainCategoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                category = new Category(result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getInt("maincategorie_id"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return category;
    }

    public List<Product> getCurses(int vestigingId) {
        //   List<Course> cursus = new ArrayList<Course>();
        List<Product> cursus = new ArrayList<Product>();
        try {
            String sql = "SELECT cr.*, p.naam, p.prijs "
                    + "FROM cursusrooster cr"
                    + " LEFT OUTER JOIN product p ON cr.productnummer = p.productnummer"
                    + " WHERE cr.idvestiging='" + vestigingId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                cursus.add(new Product(result.getInt("idcursusrooster"),
                        result.getInt("productnummer"),
                        result.getInt("idvestiging"),
                        result.getInt("studionummer"),
                        result.getString("dag"),
                        result.getString("tijd"),
                        result.getString("begingdatum"),
                        result.getString("einddatum"),
                        result.getString("van"),
                        result.getString("tot"),
                        result.getInt("maxaantal"),
                        result.getInt("huidigaantal"),
                        result.getString("naam"),
                        result.getDouble("prijs")));

            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return cursus;



    }

    //-------------------------------------------------------------------------
    //EIND OF CHRIST CODE
    //-------------------------------------------------------------------------
    //-------------------------------------------------------------------------
    //               Begin LID
    //--------------------------------------------------------------------------
    //Method ProductList is used to populate a table with rows
    public ResultSet CategorieList(ResultSet rs) {
        ResultSet result = null;
        try {
            String sql = "SELECT categorie_id, naam FROM categorie;";
            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }
    //------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public Category getCategorie(int categoryId) {
        Category category = null;
        try {
            String sql = "SELECT * FROM categorie WHERE categorie_id='" + categoryId + "'";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                category = new Category(result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"),
                        result.getInt("maincategorie_id"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return category;
    }
    //------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------  

    public Category getCategoryIdnaam(String categorienaam) {
        Category category = null;
        try {
            String sql = "SELECT categorie_id FROM categorie WHERE naam='" + categorienaam + "'";
            ResultSet result = dbmanager.doQuery(sql);

            if (result.next()) {
                category = new Category(result.getInt("categorie_id"));
            }
            System.out.println(sql);
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }

        return category;
    }

    //------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------       
    public Category NamegetMainCategoryId(String Maincategorienaam) {
        Category category = null;
        try {
            String sql = "SELECT maincategorie_id FROM maincategorie WHERE naam='" + Maincategorienaam + "'";
            ResultSet result = dbmanager.doQuery(sql);

            if (result.next()) {
                category = new Category(result.getInt("maincategorie_id"));
            }
            System.out.println(sql);
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }

        return category;
    }
    //------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public List<Category> getCategories() {
        List<Category> categories = new ArrayList<Category>();
        try {
            String sql = "SELECT * FROM categorie ORDER BY naam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Category category = new Category(result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getString("omschrijving"));
                categories.add(category);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return categories;
    }
    //------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------   

    public List<Category> getMainCategory() {
        List<Category> categories = new ArrayList<Category>();
        try {
            String sql = "SELECT * FROM maincategorie ORDER BY naam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Category category = new Category(result.getInt("maincategorie_id"),
                        result.getString("naam"));
                categories.add(category);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return categories;
    }
    //------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public void addCategory(Category categgie) {

        String sql = "INSERT INTO categorie(categorie_id, "
                + "naam, "
                + "omschrijving, "
                + "maincategorie_id) "
                + "VALUES ((SELECT Max(categorie_id)+1 FROM categorie),'"
                + categgie.getName() + "','"
                + categgie.getDescription() + "','"
                + categgie.getMainCategoryId() + "')";
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public void updateCategory(Category categorie) {
        String sql = "UPDATE categorie "
                + "SET naam='" + categorie.getName()
                + "', omschrijving='" + categorie.getDescription()
                + "', maincategorie_id='" + categorie.getMainCategoryId()
                + "' WHERE categorie_id=" + categorie.getCategoryId();

        System.out.println(sql);
        dbmanager.executeQuery(sql);
    }

    /**
     * Begin
     * medewerker------------------------------------------------------------
     *
     *
     * Zoek een medewerker op basis van een medewerkernummer
     *
     * @param medewerkerNummer datatype int
     * @return medewerker
     */
    /**
     * Zoek een medewerker op basis van een medewerkernummer en een wachtwoord
     *
     * @param medewerkerNummer datatype int
     * @param wachtWoord datatype String
     * @return medewerker
     */
    public Staff getMedewerkerWachtWoord(int medewerkerNummer,
            String wachtWoord) {

        Staff medewerker = null;

        try {
            String sql = "SELECT * FROM medewerker  WHERE medewerkernummer='"
                    + medewerkerNummer + "' and wachtwoordmedewerker = '"
                    + wachtWoord + "'";

            ResultSet result = dbmanager.doQuery(sql);

            if (result.next()) {
                medewerker = new Staff(result.getInt("medewerkernummer"),
                        result.getString("wachtwoordmedewerker"),
                        result.getInt("idfunctie"));
            }


        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return medewerker;
    }
    //--------------------------------------------------------------------------
    //Method MedewerkersList is used to populate a table with rows
    //--------------------------------------------------------------------------

    public ResultSet MedewerkersList(ResultSet res) {
        res = null;
        try {
            String sql = "SELECT medewerkernummer As ID, voornaam, tussenvoegsel, achternaam, geboortedatum from medewerker";

            res = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);
        }

        return res;
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public ResultSet MedewerkersSearch(ResultSet rs) {
        Staff staff = new Staff();
        ResultSet result = null;
        try {
            String sql = "SELECT"
                    + " medewerker.medewerkernummer,"
                    + " medewerker.voornaam || RTRIM(' '|| COALESCE(medewerker.tussenvoegsel,''))AS naam,"
                    + " medewerker.achternaam,"
                    + " medewerker.email,"
                    + " medewerker.mobieltelefoonnummer,"
                    + " functie.functienaam AS funtie,"
                    + " vestiging.vestigingnaam AS vestiging"
                    + " FROM"
                    + " medewerker,"
                    + " functie,"
                    + " medewerkersfunctie,"
                    + " vestigingsmedewerkers,"
                    + " vestiging"
                    + " WHERE"
                    + " medewerker.medewerkernummer =" + staff.getStaffId() + " AND"
                    + " medewerker.idfunctie = functie.idfunctie AND"
                    + " medewerker.idvestiging = vestiging.idvestiging";
            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }

        return result;
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public void addStaff(Staff staff) {

        String sql = "INSERT INTO medewerker(medewerkernummer, "
                + "voornaam, "
                + "tussenvoegsel, "
                + "achternaam, "
                + "geboortedatum, "
                + "geslacht, "
                + "telefoonnummer, "
                + "mobieltelefoonnummer, "
                + "email, "
                + "moddate, "
                + "wachtwoordmedewerker, "
                + "idvestiging, "
                + "idfunctie) "
                + "VALUES (DEFAULT,'"
                + staff.getFirstname() + "','"
                + staff.getMiddlename() + "','"
                + staff.getLastname() + "','"
                + staff.getBirthday() + "','"
                + staff.getGender() + "','"
                + staff.getTelephoneNumber() + "','"
                + staff.getMobilePhone() + "','"
                + staff.getEmail() + "', CURRENT_DATE, '"
                + staff.getPassword() + "','"
                + staff.getBranch() + "','"
                + staff.getFunctionId() + "')";
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public void updateStaff(Staff staff) {
        String sql = "UPDATE medewerker "
                + "SET voornaam='" + staff.getFirstname()
                + "', tussenvoegsel='" + staff.getMiddlename()
                + " ', achternaam='" + staff.getLastname()
                + "', geboortedatum='" + staff.getBirthday()
                + "', geslacht='" + staff.getGender()
                + "', telefoonnummer='" + staff.getTelephoneNumber()
                + "', mobieltelefoonnummer='" + staff.getMobilePhone()
                + "', email='" + staff.getEmail()
                + "', moddate=CURRENT_DATE,"
                + " wachtwoordmedewerker='" + staff.getPassword()
                + "', idvestiging='" + staff.getBranch()
                + "', idfunctie='" + staff.getFunctionId()
                + "' WHERE medewerkernummer=" + staff.getStaffId();

        System.out.println(sql);
        dbmanager.executeQuery(sql);
    }

    public void DeleteStaff(int medewerkernummer) {
        String sql = "DELETE FROM medewerker WHERE medewerkernummer =" + medewerkernummer;

        System.out.println(sql);
        dbmanager.executeQuery(sql);
    }
//------------------------------------------------------------------------------
// Haal alle functies op.
//------------------------------------------------------------------------------

    public List<Staff> getfunction() {
        List<Staff> funties = new ArrayList<Staff>();
        try {
            String sql = "SELECT * FROM functie ORDER BY functienaam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Staff staff = new Staff(result.getInt("idfunctie"),
                        result.getString("functienaam"));

                funties.add(staff);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return funties;
    }

    //-------------------------------------------------------------------------
    //Einde MEDEWERKER////////////////////////////////////////////////////////
    //-------------------------------------------------------------------------
    //-------------------------------------------------------------------------
    //Begin LID////////////////////////////////////////////////////////////////
    //-------------------------------------------------------------------------
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------
    public Member getLidWachtWoord(int lidNummer, String wachtWoordLid) {

        Member lid = null;

        try {
            String sql = "SELECT * FROM lid WHERE lidnummer='" + lidNummer
                    + "' and wachtwoordlid = '" + wachtWoordLid + "'";

            ResultSet result = dbmanager.doQuery(sql);

            if (result.next()) {
                lid = new Member(result.getInt("lidnummer"));
            }

        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return lid;
    }

    //---------------------------------------------------------------------
    //Met deze methode wordt nieuwe member toegevoegd aan de database
    //---------------------------------------------------------------------
    //------------------------------------------------------------------------
    // Met deze method wordt de gegevens uit de formulier toegevoed in de database
    //------------------------------------------------------------------------
//-----------------------------------------------------------------------------
//Einde LID//////////////////////////////////////////////////////////
//----------------------------------------------------------------------------
//-----------------------------------------------------------------------------
    //beging vestiging
//-----------------------------------------------------------------------------
    public Branch getVestiging(int idvestiging) {
        Branch vestiging = new Branch();
        try {
            String sql = "SELECT idvestiging, vestigingnaam, plaats  "
                    + "FROM vestiging "
                    + "WHERE idvestiging='" + idvestiging + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                vestiging = new Branch(result.getInt("idvestiging"),
                        result.getString("vestigingnaam"),
                        result.getString("plaats"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return vestiging;
    }

    //-------------------------------------------------------------------------
    //getVestigingStudioID: via de Studionaam hall  de studio id uit ////////
    //------------------------------------------------------------------------
    public Studio getStudioID(String studionaam) {
        Studio studionummer = new Studio();
        try {
            String sql = "SELECT studionummer FROM studio WHERE studionaam='" + studionaam + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                studionummer = new Studio(result.getInt("studionummer"));
            }
            System.out.println(sql);
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return studionummer;
    }

    //-------------------------------------------------------------------------
    //getVestigingID wordt gebruikt voor insert en update van medewerker
    //------------------------------------------------------------------------
    public Branch getVestigingID(String vestigingnaam) {
        Branch vestiging = new Branch();
        try {
            String sql = "SELECT idvestiging "
                    + "FROM vestiging "
                    + "WHERE vestigingnaam='" + vestigingnaam + "'";

            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                vestiging = new Branch(result.getInt("idvestiging"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }

        return vestiging;
    }

//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------
    public List<Branch> getStudioList() {
        List<Branch> vestiging = new ArrayList<Branch>();
        try {
            String sql = "SELECT * FROM studio ORDER BY studionaam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                Branch branch = new Branch(result.getInt("studionummer"),
                        result.getString("studionaam"));

                vestiging.add(branch);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return vestiging;
    }
//-----------------------------------------------------------------------------
// Einde Vesting/////////////////////////////////////////////////////////////
//----------------------------------------------------------------------------- 
//------------------------------------------------------------------------
//beging Functie////////////////////////////////////////////////////////////////
//---------------------------------------------------------------------------

    public Staff getfunctie(int idfunctie) {
        Staff staff = new Staff();
        try {
            String sql = "SELECT idfunctie, functienaam "
                    + "FROM functie "
                    + "WHERE idfunctie=" + idfunctie;
            ResultSet result = dbmanager.doQuery(sql);
            System.out.println(sql);
            if (result.next()) {
                staff = new Staff(result.getInt("idfunctie"),
                        result.getString("functienaam"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return staff;
    }

    //-------------------------------------------------------------------------
    //getfunctieID wordt gebruikt voor insert en update van medewerker
    //------------------------------------------------------------------------
    public Staff getfunctieID(String functienaam) {
        Staff staff = new Staff();
        try {
            String sql = "SELECT idfunctie "
                    + "FROM functie "
                    + "WHERE functienaam='" + functienaam + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                staff = new Staff(result.getInt("idfunctie"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return staff;
    }

    //------------------------------------------------------------------------- 
    //Einde Functie////////////////////////////////////////////////////////////
    //-------------------------------------------------------------------------
    //-------------------------------------------------------------------------
    //Begin PRODUCT////////////////////////////////////////////////////////////
    //-------------------------------------------------------------------------
    //Method ProductList is used to populate a table with rows
    public ResultSet ProductList(ResultSet rs) {
        ResultSet result = null;
        try {
            String sql = "SELECT product.productnummer,"
                    + " product.naam as productnaam,"
                    + " categorie.naam as categorie,"
                    + " '€ '|| product.prijs AS prijs"
                    + " FROM"
                    + " product"
                    + " LEFT JOIN"
                    + " categorie"
                    + " ON"
                    + " product.categorie_id = categorie.categorie_id";
            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    //------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------   
    public void addProduct(Product product) {
        String sql = "INSERT INTO product(productnummer,"
                + " categorie_id,"
                + " naam,"
                + " prijs,"
                + " description)"
                + " VALUES ((SELECT MAX(productnummer)+1 FROM product), '"
                + product.getCategorieId() + "','"
                + product.getName() + "','"
                + product.getPrice() + "','"
                + product.getDescription() + "')";
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
    }

//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------
    public void UpdateProduct(Product product) {

        String sql = "UPDATE product SET"
                + " categorie_id='" + product.getCategorieId()
                + "', naam='" + product.getName()
                + "', prijs='" + product.getPrice()
                + "', description='" + product.getDescription()
                + "' WHERE productnummer='" + product.getProductId() + "'";
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
    }

    //-------Begin factuur
    public ResultSet FactuurList(ResultSet rs) {
        ResultSet result = null;
        try {
            String sql = "SELECT factuur.factuurnummer, lid.voornaam || RTRIM(' '|| COALESCE(lid.tussenvoegsel,''))AS naam, lid.achternaam FROM public.factuur, public.lid WHERE factuur.lidnummer = lid.lidnummer";
            System.out.println(sql);
            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }

    public ArrayList getFactuur(int lidNummer) {

        ArrayList facturen = new ArrayList();

        try {
            String sql = "SELECT * FROM factuur WHERE lidnummer='" + lidNummer;

            ResultSet rows = dbmanager.doQuery(sql);

            while (rows.next()) {
                String factuurNummer = rows.getString("factuurnummer").trim();
                String jaarFactuur = rows.getString("jaarfactuur").trim();
                String maandFactuur = rows.getString("maandfactuur").trim();
                String lidFactuur = rows.getString("lid").trim();

                // Stop de gegevens van in een Object-array
                Object[] factuur = new Object[4];
                factuur[0] = factuurNummer;
                factuur[1] = jaarFactuur;
                factuur[2] = maandFactuur;
                factuur[3] = lidFactuur;
                facturen.add(factuur);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return facturen;
    }

//--------Einde Factuu
    public void setOrder(model.Basket basket, int lidnummer, String opmerking, String betaalmethode, int vestgingId) {
        String sql = "INSERT INTO barorder (ordernummer, lidnummer, betaalmethode, datum, opmerking, idvestiging)"
                + " VALUES (DEFAULT, "
                + lidnummer + ", '"
                + betaalmethode + "', CURRENT_DATE , '"
                + opmerking + "',"
                + vestgingId + ")"
                + " RETURNING ordernummer";
        int ordernummer = 0;
        try {
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                ordernummer = result.getInt(1);
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }

        // Almer,, dubbel checken of uniek factuurnummer nodig is

        String sql_factuur = "INSERT INTO factuur (factuurnummer, lidnummer, datum)"
                + " VALUES (DEFAULT, " + lidnummer + ", CURRENT_TIMESTAMP )"
                + " RETURNING factuurnummer";

        int factuurnummer = 0;

        try {
            ResultSet result = dbmanager.doQuery(sql_factuur);
            while (result.next()) {
                factuurnummer = result.getInt(1);
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }

        List<Product> products = basket.getProducts();
        for (Product product : products) {
            int productnummer = product.getProductId();
            int aantal = basket.getProductAmount(product);
            String SQL_orderProduct = "INSERT INTO orderregel (productnummer, ordernummer, aantal, factuurnummer)"
                    + " VALUES (" + productnummer + "," + ordernummer + "," + aantal + "," + factuurnummer + ")";
            // + " VALUES (" + productnummer + "," + ordernummer + "," + aantal + ")";
            dbmanager.executeQueryVoorBar(SQL_orderProduct);
        }
    }

    //--------------------------------------------------------------------------
    // BEGIN CURSUS
    //--------------------------------------------------------------------------
    public ResultSet CususList(ResultSet result) {

        try {
            String sql = "SELECT"
                    + " productnummer,"
                    + " naam,"
                    + "'€ ' || product.prijs As prijs"
                    + " from product"
                    + " WHERE categorie_id=8";
            System.out.println(sql);
            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }

//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------
    public ResultSet CususroosterList(ResultSet result) {

        try {
            String sql = "SELECT"
                    + " cursusrooster.idcursusrooster,"
                    + " product.naam,"
                    + " vestiging.vestigingnaam,"
                    + " studio.studionaam,"
                    + " cursusrooster.dag,"
                    + " cursusrooster.van As tijd,"
                    + "'€ ' || product.prijs  prijs "
                    + " FROM"
                    + " public.product,"
                    + " public.cursusrooster,"
                    + " public.studio,"
                    + " public.vestiging"
                    + " WHERE"
                    + " product.productnummer = cursusrooster.productnummer AND"
                    + " vestiging.idvestiging = cursusrooster.idvestiging AND"
                    + " studio.studionummer = cursusrooster.studionummer";

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }

//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------
    public Course getCourse(int productId) {
        Course course = new Course();
        try {
            String sql = "SELECT * FROM product WHERE productnummer=" + productId;
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                course = new Course(result.getInt("productnummer"),
                        result.getInt("categorie_id"),
                        result.getString("naam"),
                        result.getDouble("prijs"),
                        result.getString("description"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return course;
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public Course getCoursename(String coursename) {
        Course course = new Course();
        try {
            String sql = "SELECT * FROM product WHERE naam='" + coursename + "'";
            ResultSet result = dbmanager.doQuery(sql);
            if (result.next()) {
                course = new Course(result.getInt("productnummer"));
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return course;
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public Course getCususRooster(int idcursusrooster) {
        Course course = null;

        try {
            String sql = "SELECT * FROM cursusrooster WHERE idcursusrooster='" + idcursusrooster + "'";

            ResultSet result = dbmanager.doQuery(sql);

            System.out.println(sql);
            if (result.next()) {
                course = new Course(
                        result.getInt("idcursusrooster"),
                        result.getInt("productnummer"),
                        result.getInt("idvestiging"),
                        result.getInt("studionummer"),
                        result.getString("dag"),
                        result.getInt("maxaantal"),
                        result.getDate("begingdatum"),
                        result.getDate("einddatum"),
                        result.getTime("van"),
                        result.getTime("tot"));
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return course;
    }

    //--------------------------------------------------------------------------
    // insert cursus eerst daarna haal geef de IDnummer terug
    //--------------------------------------------------------------------------
    public void addCursus(Course course) {



        String sql = "INSERT INTO product(productnummer, categorie_id, naam, prijs)"
                + " VALUES ((SELECT MAX(productnummer)+1 FROM product), '"
                + 8 + "', '" + course.getCursusnaam() + "', '" + course.getPrijs() + "')"
                + " RETURNING productnummer";
        System.out.println(sql);
        int productnummer = 0;
        try {
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                productnummer = result.getInt(1);
                //System.out.println(" idactiviteit:"+idactiviteit);
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        String sql2 = "INSERT INTO cursusrooster(idcursusrooster,"
                + " productnummer,"
                + " idvestiging,"
                + " studionummer,"
                + " dag,"
                + " tijd,"
                + " begingdatum,"
                + " einddatum,"
                + " van,"
                + " tot,"
                + " maxaantal)"
                + " VALUES ((SELECT MAX(idcursusrooster)+1 FROM cursusrooster), '"
                + productnummer + "','"
                + course.getBranchID() + "','"
                + course.getStudioID() + "','"
                + course.getDag() + "','"
                + course.getTot() + "','"
                + course.getBigingdate() + "','"
                + course.getEinddate() + "','"
                + course.getVan() + "','"
                + course.getTot() + "','"
                + course.getMaxaantal() + "')";

        System.out.println(sql2);
        try {
            dbmanager.executeQuery(sql2);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }



    }
    //--------------------------------------------------------------------------
    // insert cursus eerst daarna haal geef de IDnummer terug
    //--------------------------------------------------------------------------

    public void addCursusToRooster(Course course) {


        String sql = "INSERT INTO cursusrooster(idcursusrooster,"
                + " productnummer,"
                + " idvestiging,"
                + " studionummer,"
                + " dag,"
                + " tijd,"
                + " begingdatum,"
                + " einddatum,"
                + " van,"
                + " tot,"
                + " maxaantal)"
                + " VALUES ((SELECT MAX(idcursusrooster)+1 FROM cursusrooster), '"
                + course.getCursusnummer() + "','"
                + course.getBranchID() + "','"
                + course.getStudioID() + "','"
                + course.getDag() + "','"
                + course.getTot() + "','"
                + course.getBigingdate() + "','"
                + course.getEinddate() + "','"
                + course.getVan() + "','"
                + course.getTot() + "','"
                + course.getMaxaantal() + "')";
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }


    }
    //------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public void Updatecursusrooster(Course course) {


        String sql = "UPDATE cursusrooster SET"
                + " productnummer='" + course.getCursusnummer()
                + "', idvestiging='" + course.getBranchID()
                + "', studionummer='" + course.getStudioID()
                + "', dag='" + course.getDag()
                + "', tijd='" + course.getVan()
                + "', begingdatum='" + course.getBigingdate()
                + "', einddatum='" + course.getEinddate()
                + "', van='" + course.getVan()
                + "', tot='" + course.getTot()
                + "', maxaantal='" + course.getMaxaantal()
                + "' WHERE idcursusrooster='" + course.getIdcursusrooster() + "'";
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }



    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public void Updatecursus(Course course) {

        String sql = "UPDATE product SET"
                + " naam ='" + course.getCursusnaam()
                + "', prijs ='" + course.getPrijs()
                + "', categorie_id ='8"
                + "' WHERE productnummer='" + course.getCursusnummer() + "'";

        System.out.println(sql);

        try {

            dbmanager.executeQuery(sql);


        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }


    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------
//------------------------------------------------------------------------------
// EIND CURSUS/////////////////////////////////////////////////////////////
//------------------------------------------------------------------------------

//------------------------------------------------------------------------------
// BEGIN GROUP ACTIVITY/////////////////////////////////////////////////////
//------------------------------------------------------------------------------
    public ResultSet GroupActivityList(ResultSet result) {

        try {
            String sql = "SELECT idactiviteit, activiteitnaam FROM activiteit";

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }

    public ResultSet GroupActivityroosterList(ResultSet result) {

        try {
            String sql = "SELECT"
                    + " activiteitrooster.idactiviteitrooster,"
                    + " activiteit.activiteitnaam,"
                    + " vestiging.vestigingnaam,"
                    + " studio.studionaam,"
                    + " activiteitrooster.dag,"
                    + " activiteitrooster.begintijd"
                    + " FROM"
                    + " public.activiteitrooster,"
                    + " public.activiteit,"
                    + " public.studio,"
                    + " public.vestiging"
                    + " WHERE "
                    + " activiteitrooster.idactiviteit = activiteit.idactiviteit AND"
                    + " activiteitrooster.idvestiging = vestiging.idvestiging AND"
                    + " activiteitrooster.studionummer = studio.studionummer";

            result = dbmanager.doQuery(sql);

        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e);

        }
        return result;
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public List<GroupActivity> GroupActivity() {
        List<GroupActivity> Activity = new ArrayList<GroupActivity>();
        try {
            String sql = "SELECT * FROM activiteit ORDER BY activiteitnaam ASC";
            ResultSet result = dbmanager.doQuery(sql);
            while (result.next()) {
                GroupActivity groupActivity = new GroupActivity(result.getInt("idactiviteit"),
                        result.getString("activiteitnaam"),
                        result.getString("description"));

                Activity.add(groupActivity);
            }
        } catch (SQLException e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return Activity;
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public GroupActivity getGroupActivity(int activityid) {
        GroupActivity groupactivity = null;

        try {

            String sql = "SELECT * FROM activiteit WHERE idactiviteit='" + activityid + "'";

            ResultSet result = dbmanager.doQuery(sql);

            System.out.println(sql);
            if (result.next()) {
                groupactivity = new GroupActivity(result.getInt("idactiviteit"),
                        result.getString("activiteitnaam"),
                        result.getString("description"));
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return groupactivity;
    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public GroupActivity getGroupActivityID(String activity) {
        GroupActivity groupactivity = null;

        try {

            String sql = "SELECT idactiviteit FROM activiteit WHERE activiteitnaam='" + activity + "'";

            ResultSet result = dbmanager.doQuery(sql);

            System.out.println(sql);
            if (result.next()) {
                groupactivity = new GroupActivity(result.getInt("idactiviteit"));

            } else if (!(result.next())) {
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);

        }
        return groupactivity;
    }

    //--------------------------------------------------------------------------
    // insert activiteit eerst daarna haal geef de IDnummer terug
    //--------------------------------------------------------------------------
    public void addGroupActivity(GroupActivity activity) {

        String sql = "INSERT INTO activiteit(idactiviteit, activiteitnaam, datum, description)"
                + " VALUES (DEFAULT, '" + activity.getActivityname() + "', CURRENT_DATE, '" + activity.getDescr() + "')";
        System.out.println(sql);

        try {
            dbmanager.executeQuery(sql);

        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
    }

    public void addGroupActivityRooster(GroupActivity activityrooster) {
        String sql2 = "INSERT INTO activiteitrooster(idactiviteitrooster,"
                + " idactiviteit,"
                + " idvestiging,"
                + " studionummer,"
                + " dag,"
                + " begintijd,"
                + " eindtijd,"
                + " begindatum,"
                + " einddatum)"
                + " VALUES (DEFAULT, '"
                + activityrooster.getActivityID() + "','"
                + activityrooster.getBranchID() + "','"
                + activityrooster.getStudioID() + "','"
                + activityrooster.getDag() + "','"
                + activityrooster.getBegintijd() + "','"
                + activityrooster.getEindtijd() + "','"
                + activityrooster.getBegindatum() + "','"
                + activityrooster.getEinddatum() + "')";
        System.out.println(sql2);
        try {
            dbmanager.executeQuery(sql2);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }



    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public void UpdateGroupActivity(GroupActivity activity) {



        String sql = "UPDATE activiteit SET"
                + " activiteitnaam ='" + activity.getActivityname()
                + "', datum=CURRENT_DATE"
                + ", description ='" + activity.getDescr()
                + "' WHERE idactiviteit=" + activity.getActivityID();

        System.out.println(sql);

        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }


    }
//------------------------------------------------------------------------------
//
//------------------------------------------------------------------------------

    public void UpdateGroupActivityRooster(GroupActivity activityrooster) {

        String sql = "UPDATE activiteitrooster SET"
                + " idactiviteit='" + activityrooster.getActivityID()
                + "', idvestiging='" + activityrooster.getBranchID()
                + "', studionummer='" + activityrooster.getStudioID()
                + "', dag='" + activityrooster.getDag()
                + "', begintijd='" + activityrooster.getBegintijd()
                + "', eindtijd='" + activityrooster.getEindtijd()
                + "', begindatum='" + activityrooster.getBegindatum()
                + "', einddatum='" + activityrooster.getEinddatum()
                + "' WHERE idactiviteitrooster=" + activityrooster.getActivityrooster();
        System.out.println(sql);
        try {
            dbmanager.executeQuery(sql);
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }



    }

    public void deleteGroupActivity(GroupActivity activitydelete) {
    }

    //--------------------------------------------------------------------------
    // BEGIN EIND ACTIVITY/////////////////////////////////////////////////////
    //--------------------------------------------------------------------------
//-----------------------------------------------------------------------------
//get id cursus from item selected
//------------------------------------------------------------------------------
    public void DeleteRow(String table_name, String column, int ID) {

        StringBuilder sql = new StringBuilder(512);
        sql.append("DELETE FROM ").append(table_name);
        sql.append(" WHERE ").append(column).append("=").append(ID);
        System.out.println(sql.toString());
        try {
            dbmanager.executeQuery(sql.toString());

        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }

    }

    public int getRoostervergelijke(int vestigingId, int studioId, Date Begingdatum, Date Einddatum, String dag, String begintijd, String Eindtijd) {
        ResultSet result;
        int rs = 0;
        String sql = "SELECT * "
                + " FROM"
                + " cursusrooster"
                + " WHERE"
                + " idvestiging ='" + vestigingId + "' AND"
                + " studionummer ='" + studioId + "' AND"
                + " dag = '" + dag + "' AND ('" + Begingdatum + "' BETWEEN begingdatum AND einddatum"
                + " OR '" + Einddatum + "' BETWEEN begingdatum AND einddatum"
                + " OR '" + Begingdatum + "' <= begingdatum AND '" + Einddatum + "' >= einddatum) AND"
                + " ('" + begintijd + "' BETWEEN van AND tot"
                + " OR '" + Eindtijd + "' BETWEEN van AND tot"
                + " OR '" + begintijd + "' <= van AND '" + Eindtijd + "' >= tot)";

        System.out.println(sql);
        try {
            result = dbmanager.doQuery(sql);
            if (result.next()) {
                rs = result.getInt(1);
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return rs;

    }

    public int getRoostervergelijkeActiviteit(int vestigingId, int studioId, Date Begingdatum, Date Einddatum, String dag, String begintijd, String Eindtijd) {
        ResultSet result;
        int rs = 0;
        String sql = "SELECT * "
                + " FROM"
                + " activiteitrooster"
                + " WHERE"
                + " idvestiging ='" + vestigingId + "' AND"
                + " studionummer ='" + studioId + "' AND"
                + " dag = '" + dag + "' AND ('" + Begingdatum + "' BETWEEN begindatum AND einddatum"
                + " OR '" + Einddatum + "' BETWEEN begindatum AND einddatum"
                + " OR '" + Begingdatum + "' <= begindatum AND '" + Einddatum + "' >= einddatum) AND"
                + " ('" + begintijd + "' BETWEEN begintijd AND eindtijd"
                + " OR '" + Eindtijd + "' BETWEEN begintijd AND eindtijd"
                + " OR '" + begintijd + "' <= begintijd AND '" + Eindtijd + "' >= eindtijd)";

        System.out.println(sql);
        try {
            result = dbmanager.doQuery(sql);
            if (result.next()) {
                rs = result.getInt(1);
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return rs;

    }
    //-----------------------------------------------------------------------------
//get id cursus from item selected
//------------------------------------------------------------------------------

    public int SelectRow(String table_name, String column, int ID) {
        ResultSet result;
        int res = 0;
        StringBuilder sql = new StringBuilder(512);
        sql.append("SELECT * FROM ").append(table_name);
        sql.append(" WHERE ").append(column).append("=").append(ID);
        System.out.println(sql.toString());
        try {
            result = dbmanager.doQuery(sql.toString());
            if (result.next()) {

                res = result.getInt(1);
            }
        } catch (Exception e) {
            System.err.println(DbManager.SQL_EXCEPTION + e.getMessage());
            e.printStackTrace(System.err);
        }
        return res;


    }
}
